﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Feedbacks
{
    internal static class FeedbackDAL
    {
        internal static FeedbackEntity GetFeedbackByReader(IDataReader dr)
        {
            FeedbackEntity feedback = new FeedbackEntity();

            feedback.FeedbackID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            feedback.SellerID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            feedback.Content = dr.IsDBNull(2) ? null : dr.GetString(2);
            feedback.Created = Util.ConverDateTime(dr.GetValue(3));

            return feedback;
        }

        internal static int GetFeedbackCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectFeedback = @"SELECT COUNT([FeedbackID]) FROM [Shop_Feedback]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectFeedback, ref parameterlst);
            DbCommand SelectFeedbackCommand = db.GetSqlStringCommand(SelectFeedback);
            DbHelper.PackDbCommandByDbParameters(SelectFeedbackCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectFeedbackCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<FeedbackEntity> GetFeedbackList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<FeedbackEntity> feedbacklst = new List<FeedbackEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectFeedback = @"
                    SELECT [FeedbackID],[SellerID],[Content],[Created]
                    FROM (
                        SELECT [FeedbackID],[SellerID],[Content],[Created], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectFeedback += " " + sortField + " " + sortMode + ",";
            SelectFeedback += @" [Shop_Feedback].[FeedbackID] Desc
                    ) AS rowNum 
                        FROM [Shop_Feedback] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectFeedback, ref parameterlst);
            SelectFeedback += @"
                    ) AS [Shop_Feedback] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectFeedbackCommand = db.GetSqlStringCommand(SelectFeedback);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectFeedbackCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectFeedbackCommand))
            {
                while (dr.Read())
                {
                    FeedbackEntity feedback = GetFeedbackByReader(dr);

                    feedbacklst.Add(feedback);
                }
            }

            return feedbacklst;
        }

        internal static FeedbackEntity GetFeedback(int feedbackID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            FeedbackEntity feedback = null;

            string SelectFeedback = @"
                    SELECT [FeedbackID],[SellerID],[Content],[Created]
                    FROM [Shop_Feedback] 
                    WHERE [Shop_Feedback].FeedbackID=@FeedbackID
                ";

            DbCommand SelectFeedbackCommand = db.GetSqlStringCommand(SelectFeedback);
            db.AddInParameter(SelectFeedbackCommand, "@FeedbackID", DbType.Int32, feedbackID);

            using (IDataReader dr = db.ExecuteReader(SelectFeedbackCommand))
            {
                if (dr.Read())
                {
                    feedback = GetFeedbackByReader(dr);
                }
            }

            return feedback;
        }

        internal static int InsertFeedback(FeedbackEntity feedback)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertFeedback = @"INSERT INTO [Shop_Feedback] 
				([SellerID],[Content],[Created])
				 VALUES (@SellerID,@Content,@Created)";

            DbCommand InsertFeedbackCommand = db.GetSqlStringCommand(InsertFeedback);
            db.AddInParameter(InsertFeedbackCommand, "@SellerID", DbType.Int32, feedback.SellerID);
            db.AddInParameter(InsertFeedbackCommand, "@Content", DbType.String, feedback.Content);
            db.AddInParameter(InsertFeedbackCommand, "@Created", DbType.DateTime, feedback.Created);

            result = db.ExecuteNonQuery(InsertFeedbackCommand);

            return result;
        }

        internal static int UpdateFeedback(FeedbackEntity feedback)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateFeedback = @"UPDATE [Shop_Feedback] 
				 SET [SellerID]=@SellerID,[Content]=@Content,[Created]=@Created 
				 WHERE [FeedbackID] = @FeedbackID";

            DbCommand UpdateFeedbackCommand = db.GetSqlStringCommand(UpdateFeedback);
            db.AddInParameter(UpdateFeedbackCommand, "@FeedbackID", DbType.Int32, feedback.FeedbackID);
            db.AddInParameter(UpdateFeedbackCommand, "@SellerID", DbType.Int32, feedback.SellerID);
            db.AddInParameter(UpdateFeedbackCommand, "@Content", DbType.String, feedback.Content);
            db.AddInParameter(UpdateFeedbackCommand, "@Created", DbType.DateTime, feedback.Created);

            result = db.ExecuteNonQuery(UpdateFeedbackCommand);

            return result;
        }

        internal static int DeleteFeedback(int feedbackID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteFeedback = @"
                DELETE FROM [Shop_Feedback] WHERE FeedbackID=@FeedbackID";

            DbCommand DeleteFeedbackCommand = db.GetSqlStringCommand(DeleteFeedback);
            db.AddInParameter(DeleteFeedbackCommand, "@FeedbackID", DbType.Int32, feedbackID);
            result = db.ExecuteNonQuery(DeleteFeedbackCommand);

            return result;
        }
    }
}
